/********************************************/
  // Creation History
  // --------------------------------------------------------------
  //	Company:			HTHK
  //	NAME:				WeeklyTimeSheetInput.java
  //    Title   :			input TimeSheet Records
  //	FUNCTION:
  //	BY:					Jet Tao
  //	DATE:				11/12/2003
  // UPDATE BY:			Beyes Xia
  //	UPDATE DATE:			2004-1-9 19:37
  // LAST UPDATE DATE:		2004-1-12 16:42
  // --------------------------------------------------------------
/*********************************************/

package tss.weeklytimesheet;

import javax.servlet.*;
import javax.servlet.http.*;

import org.apache.log4j.Logger;

import java.io.*;
import java.util.*;
import java.sql.*;

import com.ffpc.sql.*;
import com.ffpc.struts.StrutsMessage;
import com.util.JdbcUtil;

import tss.common.DBServlet2;

/**
 * A Class class.
 * <P>
 * @author HTHK
 */
public class WeeklyTimeSheetInput
  extends DBServlet2 {
	private String before_date="";
	private String after_date="";
	private Logger logger = Logger.getLogger(WeeklyTimeSheetInput.class);
  @Override
protected String perform(  HttpServletRequest request,
                             HttpServletResponse response,
                             StrutsMessage message)
    throws  ServletException,
            IOException,
            SQLException,
            DataException,
            Exception
  {
	  String operation_type=request.getParameter("operation_type");
	  String url=null;
//	  message.setMessage("In TimeSheetInput");
	  logger.debug("operation_type:"+operation_type);
	 try
	  {
	  if(operation_type.equals("edit"))
		  url=viewData(request,message);
	  else if(operation_type.equals("save"))
		  url=saveData(request,message);
	  }
	  catch(Exception e)
	  {
		  logger.error(e, e);
	  }
	  finally
	  {
//	  message.setMessage("out of TimeSheetInput");
	  return url;
	  }
  }

	private String saveData(HttpServletRequest request,StrutsMessage message)
    throws  ServletException,
            SQLException,
            Exception
	{
//	    message.setMessage("In saveData");
	    logger.debug("In save Data");
	    String staff_no=request.getParameter("staff_no");
	    String which_week=request.getParameter("which_week");
	    String position=request.getParameter("position");
	    int total_record=Integer.parseInt(request.getParameter("total_record"));
	    String start_date=request.getParameter("selected_date");

		String[] enable_flag = request.getParameterValues("enable_flag");
		String[] project = request.getParameterValues("project");
		String[] job = request.getParameterValues("job");
//****** Add By Beyes Xia
//		String[] otvalue = request.getParameterValues("otvalue");
//		logger.debug(otvalue==null?"NULL":""+otvalue.length);
//		for(int i=0;i<otvalue.length;i++)
//		{
//			logger.debug("otvalue["+i+"]:"+otvalue[i]);
//		}		
//******
		String[] sun = request.getParameterValues("sun");
		String[] mon = request.getParameterValues("mon");
		String[] tue = request.getParameterValues("tue");
		String[] wed = request.getParameterValues("wed");
		String[] thu = request.getParameterValues("thu");
		String[] fri = request.getParameterValues("fri");
		String[] sat = request.getParameterValues("sat");

		String[] enanble_project = new String[total_record];
		String[] enanble_job = new String[total_record];
//****** Add By Beyes Xia	
//		String[] enanble_otValue = new String[total_record];
//******
	    request.setAttribute("SaveFlag","false");//set saving flag
		//check which project and job are enable.
		int index=0;
		for(int i=0;i<enable_flag.length;i++)
		{
			if(enable_flag[i].equals("1"))
			{
				enanble_project[index]=project[i];
				enanble_job[index]=job[i];
//****** Add By Beyes Xia				
//				enanble_otValue[index]=otvalue[i];
//******
				index++;
			}
		}
		//delete all records of this staff_no in this week
	    Statement stmt=null;
	    PreparedStatement pstmt=null;
		boolean commitState = this.getConnection().getAutoCommit();
		this.getConnection().setAutoCommit(false);
	    String sql=" delete from tss.tss_t_timesheet"+
				 " where staff_no='"+staff_no+"'"+
				 " and to_char(start_date,'YYYY-MM-DD')='"+start_date+"'";
	    String sql_insert=" insert into tss.tss_t_timesheet("+
				 " staff_no,grade,start_date,project_name,job_name,"+
				 " sunday_spend,monday_spend,tuesday_spend,"+
				 " wednesday_spend,thursday_spend,friday_spend,"+
				 " saturday_spend,last_upd_date)"+
				 " values (?,?,to_date(?,'YYYY-MM-DD'),?,?,?,?,?,?,?,?,?,sysdate)";

		logger.debug("There are "+total_record+" records need to save.");

		try
		{
			stmt=this.getConnection().createStatement();
			stmt.executeUpdate(sql);
			pstmt=this.getConnection().prepareStatement(sql_insert);
			
				for(int i=0;i<total_record;i++)
				{
					logger.debug("project="+enanble_project[i]);
					logger.debug("job="+enanble_job[i]);
//****** Add By Beyes Xia
//					logger.debug("overtime="+enanble_otValue[i]);
//******
					logger.debug("sun="+sun[i]);
					logger.debug("mon="+mon[i]);
					logger.debug("tue="+tue[i]);
					logger.debug("wed="+wed[i]);
					logger.debug("thu="+thu[i]);
					logger.debug("fri="+fri[i]);
					logger.debug("sat="+sat[i]);
					pstmt.setString(1,staff_no);
					pstmt.setString(2,position);
					pstmt.setString(3,start_date);
					pstmt.setString(4,enanble_project[i]);
					pstmt.setString(5,enanble_job[i]);
					pstmt.setString(6,sun[i]);
					pstmt.setString(7,mon[i]);
					pstmt.setString(8,tue[i]);
					pstmt.setString(9,wed[i]);
					pstmt.setString(10,thu[i]);
					pstmt.setString(11,fri[i]);
					pstmt.setString(12,sat[i]);
//****** Add By Beyes Xia					
//					pstmt.setString(13,enanble_otValue[i]);
//******				
					pstmt.executeUpdate();
				}

			request.setAttribute("SaveFlag","true");//set saving flag
			this.getConnection().commit();
		}
		catch(SQLException sqle)
		{
			logger.error(sqle, sqle);
			this.getConnection().rollback();
		}
		catch(Exception e)
		{
			logger.error(e, e);
			this.getConnection().rollback();
		}
		finally
		{
			this.getConnection().setAutoCommit(commitState);
		/*	if(stmt!=null)
				stmt.close();
			if(pstmt!=null)
				pstmt.close();
		*/
			// IISZ added in 2013-07-11 begin
			JdbcUtil.release(stmt);
			JdbcUtil.release(pstmt);
			// IISZ added in 2013-07-11 end
			return "tss/weeklytimesheet/WeeklyTimeSheetMessage.jsp";
		}
	}
	private String viewData(HttpServletRequest request,StrutsMessage message)
    throws  ServletException,
            SQLException,
            Exception
	{
//      message.setMessage("In viewData");
	  logger.debug("In View data");
	  String staff_no=request.getParameter("staff_no");
	  String which_week=request.getParameter("which_week");
	  String now_date=request.getParameter("now_date");
	  String grade=null;
	  logger.debug("now_date:"+now_date);
//****** Add By Beyes Xia
	  String staffName = "";
//******
	  String start_date=null;
	  String search_date=request.getParameter("selected_date");
	  String go_to_date=request.getParameter("go_to_date");
	  logger.debug("go_to_date:"+go_to_date);
	  logger.debug("selected_date="+search_date);
	  logger.debug("staff_no="+staff_no);
	  logger.debug("which_week="+which_week);
	  ResultSet rs=null;
	  Statement stmt=null;
	  Vector v_time_sheet=new Vector();
	  String[] a_week=new String[7];
	  int add_num=0;
	  int counter=0;
	  // IISZ add  try{}catch{}finally{} in 2013-07-05 begin
	  try {
		  
		  if(which_week!=null)
		  {
			  if(which_week.equals("0"))//last week;
			  {
				  add_num=7;
				  start_date=add_date(search_date,-7);
			  }
			  else if(which_week.equals("2"))//next week;
			  {
				  add_num=-7;
				  start_date=add_date(search_date,7);
			  }
			  else if(which_week.equals("1"))//this week;
				  start_date=get_sunday(search_date);
	//******	Add By Beyes
			  else if(which_week.equals("3"))//Go To Date;
			  {
				  start_date=get_sunday(go_to_date);
				  logger.debug("search_date:"+search_date);
				  logger.debug("start_date:"+start_date);
				  add_num = getGoToDateAddNum(search_date,start_date);
			  }
	//******
		  }
		  //////////////////
		  getRestrictDate(get_sunday(now_date));
		logger.debug("before_date="+before_date);
		logger.debug("after_date="+after_date);
		  //get history data
		  String sql=" Select project_name,job_name,"+//2
					 " sunday_spend,monday_spend,tuesday_spend,"+//3
					 " wednesday_spend,thursday_spend,"+//2
					 " friday_spend,saturday_spend,"+//,to_char(start_date,'YYYY-MM-DD')"+//3
	//******Add By Beyes Xia
					 "overtime_ind"+			
	//**********************
					 " from tss.tss_t_timesheet"+
					 " where staff_no='"+staff_no+"'"+
					 " and to_date('"+search_date+"','YYYY-MM-DD')-start_date>="+add_num+
					 " and to_date('"+search_date+"','YYYY-MM-DD')-start_date<"+(add_num+7)+
					 " and to_date('"+before_date+"','YYYY-MM-DD')<=start_date"+
					 " and to_date('"+after_date+"','YYYY-MM-DD')>=start_date";
		logger.debug("search_date="+search_date);
		logger.debug("add_num="+add_num);
		logger.debug("sql="+sql);
	
		//get "position,staff Name"
		  String sql_get_position=" Select position,"+
	//****** Add By Beyes Xia
					"alias,surname"+
	//******
				 " from TSS.TSS_C_EMPLOYEE"+
				 " where staff_no='"+staff_no+"'";
		  
		  stmt=this.getConnection().createStatement();
		  rs=stmt.executeQuery(sql);
		 
		  while(rs.next())
		  {
		 	  counter++;
			  String[] tmp_timesheet=new String[10];
			  tmp_timesheet[0]=rs.getString(1);
			  tmp_timesheet[1]=rs.getString(2);
	
			  tmp_timesheet[2]=(rs.getString(3)==null)?"":rs.getString(3);
			  tmp_timesheet[3]=(rs.getString(4)==null)?"":rs.getString(4);
			  tmp_timesheet[4]=(rs.getString(5)==null)?"":rs.getString(5);
			  tmp_timesheet[5]=(rs.getString(6)==null)?"":rs.getString(6);
			  tmp_timesheet[6]=(rs.getString(7)==null)?"":rs.getString(7);
			  tmp_timesheet[7]=(rs.getString(8)==null)?"":rs.getString(8);
			  tmp_timesheet[8]=(rs.getString(9)==null)?"":rs.getString(9);
	//******Add By Beyes Xia
			  tmp_timesheet[9]=rs.getString(10);
	//**********************
			  v_time_sheet.add(tmp_timesheet);
		  }
		logger.debug("Counter="+counter);
	
		  rs=stmt.executeQuery(sql_get_position);
		  if(rs.next())
		  {
			  grade=rs.getString(1);
			  staffName = rs.getString(2)+" "+rs.getString(3);
		  }
	
		  if(start_date!=null)
		  {
			  a_week[0]=add_date(start_date,0);
			  a_week[1]=add_date(start_date,1);
			  a_week[2]=add_date(start_date,2);
			  a_week[3]=add_date(start_date,3);
			  a_week[4]=add_date(start_date,4);
			  a_week[5]=add_date(start_date,5);
			  a_week[6]=add_date(start_date,6);		
		  }
		  String sql_last_next=" Select to_date('"+start_date+"','YYYY-MM-DD')-to_date('"+this.before_date+"','YYYY-MM-DD')-7,"+
					 "to_date('"+this.after_date+"','YYYY-MM-DD')-to_date('"+start_date+"','YYYY-MM-DD')-7"+
					 " from dual";
		  rs=stmt.executeQuery(sql_last_next);
	
		  if(rs.next())
		  {
			  logger.debug("sql_last_next="+sql_last_next);
			  logger.debug("1="+rs.getString(1));
			  logger.debug("2="+rs.getString(1));
			  if(rs.getFloat(1)>=0)
		          request.setAttribute("last_flag","false");
			  else
		          request.setAttribute("last_flag","true");
			  if(rs.getFloat(2)>=0)
		          request.setAttribute("next_flag","false");
			  else
		          request.setAttribute("next_flag","true");
		  }
	  
	  } finally {
		  JdbcUtil.release(stmt, rs);
	  }
	  // IISZ add  try{}catch{}finally{} in 2013-07-05 end
	  if(counter!=0)
		request.setAttribute("TimeSheet",v_time_sheet);
	  
	  // IISZ deleted in 2013-07-11 begin
	  // logger.debug("22222222222222");
	  // IISZ deleted in 2013-07-11 end
	  
      request.setAttribute("before_date",this.before_date);
      request.setAttribute("last_date",add_date(this.after_date,7));
      request.setAttribute("Grade",grade);
//****** Add By Beyes Xia
      request.setAttribute("StaffName",staffName);
//******
      request.setAttribute("StartDate",start_date);
      request.setAttribute("StaffNo",staff_no);
      request.setAttribute("AWeek",a_week);
      
      // IISZ deleted in 2013-07-11 begin
      // logger.debug("33333333333333333333333333");
      // IISZ deleted in 2013-07-11 end
      
      return "tss/weeklytimesheet/WeeklyTimeSheetMaintenance.jsp";

	}
	private String add_date(String date,int n)
	{
		Calendar temp=Calendar.getInstance();
		String sunday="";
		int year=Integer.parseInt(date.substring(0,4));
		int month=Integer.parseInt(date.substring(5,7));
		int day=Integer.parseInt(date.substring(8,10));
		temp.set(year,month-1,day);
		temp.add(Calendar.DATE,n);
		String monthStr="";
		String dayStr="";
		monthStr=(temp.get(Calendar.MONTH)>=9?""+(temp.get(Calendar.MONTH)+1):"0"+(temp.get(Calendar.MONTH)+1));
		dayStr=(temp.get(Calendar.DAY_OF_MONTH)>=10?""+temp.get(Calendar.DAY_OF_MONTH):"0"+temp.get(Calendar.DAY_OF_MONTH));
		sunday=""+temp.get(Calendar.YEAR)+"-"+monthStr+"-"+dayStr;
		return sunday;
	}
	private String get_sunday(String date)
	{
		Calendar temp=Calendar.getInstance();
		String sunday="";
		int year=Integer.parseInt(date.substring(0,4));
		int month=Integer.parseInt(date.substring(5,7));
		int day=Integer.parseInt(date.substring(8,10));
		temp.set(year,month-1,day);
		temp.add(Calendar.DATE,1-temp.get(Calendar.DAY_OF_WEEK));
		String monthStr="";
		String dayStr="";
		monthStr=(temp.get(Calendar.MONTH)>=9?""+(temp.get(Calendar.MONTH)+1):"0"+(temp.get(Calendar.MONTH)+1));
		dayStr=(temp.get(Calendar.DAY_OF_MONTH)>=10?""+temp.get(Calendar.DAY_OF_MONTH):"0"+temp.get(Calendar.DAY_OF_MONTH));
		sunday=""+temp.get(Calendar.YEAR)+"-"+monthStr+"-"+dayStr;
		logger.debug("sunday="+sunday);
		return sunday;
	}
	private void getRestrictDate(String date)
		throws SQLException
	{
		Statement stmt=null;
		ResultSet rs=null;
		String sql=" select before_quantity,after_quantity "+
			" from tss.tss_c_restrict";
		try
		{
			stmt=this.getConnection().createStatement();
			rs=stmt.executeQuery(sql);
			if(rs.next())
			{
				int before=rs.getInt(1);
				int after=rs.getInt(2);
				this.before_date=add_date(date,before*(-7));
				this.after_date=add_date(date,after*7);
			}
			
		}
		catch(SQLException sqle)
		{
			logger.error(sqle, sqle);
		}
		finally
		{
			/*if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();*/
			// IISZ added in 2013-07-11 begin
			JdbcUtil.release(stmt, rs);
			// IISZ added in 2013-07-11 end
		}
	}
	
//****** Add By Beyes Xia
	private int getGoToDateAddNum(String v_search_date,String v_go_to_date)
		throws SQLException
	{
		Statement stmt = null;
		ResultSet rs = null;
		int v_add_num = 0;
		String sql="select to_date('"+v_search_date+"','YYYY-MM-DD')-to_date('"+v_go_to_date+"','YYYY-MM-DD') from dual";
		logger.debug(sql);
		try
		{
			stmt=this.getConnection().createStatement();
			rs=stmt.executeQuery(sql);
			if(rs.next())
			{
				v_add_num = rs.getInt(1);
				logger.debug("add_num:"+v_add_num);
			}
			
			return v_add_num;
		}
		catch(SQLException sqle)
		{
			logger.error(sqle, sqle);
			return 0;
		}
		finally
		{
			/*if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();*/
			
			// IISZ added in 2013-07-11 begin
			JdbcUtil.release(stmt, rs);
			// IISZ added in 2013-07-11 end
		}
	}
//******

}


